﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.Services;
using System.Data.SqlClient;
using System.Data;

namespace WS
{
    /// <summary>
    /// Summary description for Service1
    /// </summary>
    [WebService(Namespace = "http://tempuri.org/")]
    [WebServiceBinding(ConformsTo = WsiProfiles.BasicProfile1_1)]
    [System.ComponentModel.ToolboxItem(false)]
    // To allow this Web Service to be called from script, using ASP.NET AJAX, uncomment the following line. 
    // [System.Web.Script.Services.ScriptService]
    public class Service1 : System.Web.Services.WebService
    {

        [WebMethod]
        public void InserirCliente(Cliente c)
        {
            string strConn = "Data Source=localhost;"
                + "Initial Catalog=PDSD;"
                + "User ID=sa;Password=senha";
            SqlConnection conn = new SqlConnection(strConn);
            SqlCommand cmd = new SqlCommand("insert into Cliente values("
                + "@apelido, @bairro, @cep, @complemento, @dataNascimento, @endereco, @nome, @numero, @sexo, @webSite)", conn);
            conn.Open();

            //cmd.Parameters.AddWithValue("@id", c.id);
            cmd.Parameters.AddWithValue("@apelido", c.apelido);
            cmd.Parameters.AddWithValue("@bairro", c.bairro);
            cmd.Parameters.AddWithValue("@cep", c.cep);
            cmd.Parameters.AddWithValue("@complemento", c.complemento);
            cmd.Parameters.AddWithValue("@dataNascimento", c.dataNascimento);
            cmd.Parameters.AddWithValue("@endereco", c.endereco);
            cmd.Parameters.AddWithValue("@nome", c.nome);
            cmd.Parameters.AddWithValue("@numero", c.numero);
            cmd.Parameters.AddWithValue("@sexo", c.sexo);
            cmd.Parameters.AddWithValue("@webSite", c.webSite);
            cmd.ExecuteNonQuery();

            conn.Close();
        }

        [WebMethod]
        public void AtualizarCliente(int id, string apelido, string bairro, string cep, string complemento, string dataNascimento, string endereco, string nome, string numero, string sexo, string webSite)
        {
            string strConn = "Data Source=localhost;"
                + "Initial Catalog=PDSD;"
                + "User ID=sa;Password=senha";
            SqlConnection conn = new SqlConnection(strConn);
            SqlCommand cmd = new SqlCommand("update  Cliente set apelido = @apelido,"
                + "bairro= @bairro,cep = @cep,complemento= @complemento,dataNascimento= @dataNascimento,endereco= @endereco,nome= @nome,numero= @numero,sexo= @sexo,webSite= @webSite  where id = @id", conn);
            conn.Open();

            cmd.Parameters.AddWithValue("@id", id);
            cmd.Parameters.AddWithValue("@apelido", apelido);
            cmd.Parameters.AddWithValue("@bairro", bairro);
            cmd.Parameters.AddWithValue("@cep", cep);
            cmd.Parameters.AddWithValue("@complemento", complemento);
            cmd.Parameters.AddWithValue("@dataNascimento", dataNascimento);
            cmd.Parameters.AddWithValue("@endereco", endereco);
            cmd.Parameters.AddWithValue("@nome", nome);
            cmd.Parameters.AddWithValue("@numero", numero);
            cmd.Parameters.AddWithValue("@sexo", sexo);
            cmd.Parameters.AddWithValue("@webSite", webSite);
            cmd.ExecuteNonQuery();

            conn.Close();
        }

        [WebMethod]
        public void InserirTarefa(string descricao, string grauTarefa, int prioridade, string status, int tempoEstimado, string tipoTarefa, string tituloTarefa, int projeto_id, int usuario_id)
        {
            string strConn = "Data Source=localhost;"
                + "Initial Catalog=PDSD;"
                + "User ID=sa;Password=senha";
            SqlConnection conn = new SqlConnection(strConn);
            SqlCommand cmd = new SqlCommand("insert into Tarefa values("
                + "@dataCadastro, @dataInicio, @descricao,"
                + " @grauTarefa, @novaTarefa, @hora, @minuto, @porcentagem, @prioridade,"
                + " @status, @tempoEstimado, @tempoTotalTrabalho, @tipoTarefa,"
                + " @tituloTarefa, @projeto_id, @usuario_id)", conn);
            conn.Open();

            cmd.Parameters.AddWithValue("@dataCadastro", DateTime.Now);
            cmd.Parameters.AddWithValue("@dataInicio", DateTime.Now);
            cmd.Parameters.AddWithValue("@descricao", descricao);

            //cmd.Parameters.AddWithValue("@finalizado", 0);

            cmd.Parameters.AddWithValue("@grauTarefa", grauTarefa);
            cmd.Parameters.AddWithValue("@novaTarefa", 1); // NOVA TABELA NO BANCO
            cmd.Parameters.AddWithValue("@hora", 0);
            cmd.Parameters.AddWithValue("@minuto", 0);
            cmd.Parameters.AddWithValue("@porcentagem", 0);
            cmd.Parameters.AddWithValue("@prioridade", prioridade);
            cmd.Parameters.AddWithValue("@status", status);
            cmd.Parameters.AddWithValue("@tempoEstimado", tempoEstimado);
            cmd.Parameters.AddWithValue("@tempoTotalTrabalho", 0);
            cmd.Parameters.AddWithValue("@tipoTarefa", tipoTarefa);
            cmd.Parameters.AddWithValue("@tituloTarefa", tituloTarefa);
            cmd.Parameters.AddWithValue("@projeto_id", projeto_id);
            cmd.Parameters.AddWithValue("@usuario_id", usuario_id);

            cmd.ExecuteNonQuery();

            conn.Close();
        }

        [WebMethod]
        public void InserirFuncionario(long id, string nome, double salario, string cargo, int custoHora, int horasDiarias, int diaPagamento, string dataAdimissao, int idSetor, string cep, string cidade, string endereco, string estado, int numero)
        {
            string strConn = "Data Source=localhost;"
                + "Initial Catalog=PDSD;"
                + "User ID=sa;Password=senha";
            SqlConnection conn = new SqlConnection(strConn);
            SqlCommand cmd = new SqlCommand("insert into Funcionario values("
                + "@id, @nome, @salario, @cargo, @custoHora, @horasDiarias, @diaPagamento, @dataAdimissao, @idSetor, @cep, @cidade, @endereco, @estado, @numero)", conn);
            conn.Open();

            cmd.Parameters.AddWithValue("@id", id);
            // cmd.Parameters.AddWithValue("@cep", cep);
            // cmd.Parameters.AddWithValue("@endereco", endereco);
            // cmd.Parameters.AddWithValue("@cidade", cidade);
            // cmd.Parameters.AddWithValue("@estado", estado);
            // cmd.Parameters.AddWithValue("@numero", numero);
            // cmd.Parameters.AddWithValue("@password", password);
            // cmd.Parameters.AddWithValue("@username", username);
            // cmd.Parameters.AddWithValue("@tipoUsuario", tipoUsuario);
            cmd.Parameters.AddWithValue("@nome", nome);
            cmd.Parameters.AddWithValue("@salario", salario);
            cmd.Parameters.AddWithValue("@cargo", cargo);
            cmd.Parameters.AddWithValue("@custoHora", custoHora);
            cmd.Parameters.AddWithValue("@horasDiarias", horasDiarias);
            cmd.Parameters.AddWithValue("@diaPagamento", diaPagamento);
            cmd.Parameters.AddWithValue("@dataAdimissao", dataAdimissao);
            cmd.Parameters.AddWithValue("@idSetor", idSetor);
            //cmd.Parameters.AddWithValue("@password", password);
            //cmd.Parameters.AddWithValue("@username", username);
            cmd.Parameters.AddWithValue("@CEP", cep);
            cmd.Parameters.AddWithValue("@endereco", endereco);
            cmd.Parameters.AddWithValue("@cidade", cidade);
            cmd.Parameters.AddWithValue("@estado", estado);
            cmd.Parameters.AddWithValue("@numero", numero);
            //cmd.Parameters.AddWithValue("@tipoUsuario", tipoUsuario);

            cmd.ExecuteNonQuery();

            conn.Close();
        }

        [WebMethod]
        public void InserirUsuario(string password, string tipoUsuario, string username,long setor_id, string nome )
        {
            string strConn = "Data Source=localhost;"
                + "Initial Catalog=PDSD;"
                + "User ID=sa;Password=senha";
            SqlConnection conn = new SqlConnection(strConn);
            SqlCommand cmd = new SqlCommand("insert into Usuario values("
                + "@password, @tipoUsuario, @username, @setor_id, @nome )", conn);
            conn.Open();

            //cmd.Parameters.AddWithValue("@id", id);
            // cmd.Parameters.AddWithValue("@cep", cep);
            // cmd.Parameters.AddWithValue("@endereco", endereco);
            // cmd.Parameters.AddWithValue("@cidade", cidade);
            // cmd.Parameters.AddWithValue("@estado", estado);
            // cmd.Parameters.AddWithValue("@numero", numero);
            // cmd.Parameters.AddWithValue("@password", password);
            // cmd.Parameters.AddWithValue("@username", username);
            // cmd.Parameters.AddWithValue("@tipoUsuario", tipoUsuario);

            cmd.Parameters.AddWithValue("@password", password);
            cmd.Parameters.AddWithValue("@tipoUsuario", tipoUsuario);
            cmd.Parameters.AddWithValue("@username", username);
            cmd.Parameters.AddWithValue("@Setor_id", setor_id);
            cmd.Parameters.AddWithValue("@nome", nome);

            cmd.ExecuteNonQuery();

            conn.Close();
        }

        [WebMethod]
        public void AtualizarFuncionario(int id, string nome, int salario, string cargo, int custoHora, int horasDiarias, int diaPagamento, string dataAdimissao, int idSetor, string cep, string cidade, string endereco, string estado, int numero)
        {
            string strConn = "Data Source=localhost;"
                + "Initial Catalog=PDSD;"
                + "User ID=sa;Password=senha";
            SqlConnection conn = new SqlConnection(strConn);
            SqlCommand cmd = new SqlCommand("update Funcionario set id = @id,"
                +  "nome = @nome, salario = @salario, cargo =@cargo,custoHora = @custoHora,horasDiarias =@horasDiarias, diaPagamento = @diaPagamento, dataAdimissao= @dataAdimissao,idSetor =  @idSetor, cep = @cep, cidade= @cidade, endereco = @endereco, estado= @estado, numero = @numero where id = @id", conn);
            conn.Open();

            cmd.Parameters.AddWithValue("@id", id);
            cmd.Parameters.AddWithValue("@nome", nome);
            cmd.Parameters.AddWithValue("@salario", salario);
            cmd.Parameters.AddWithValue("@cargo", cargo);
            cmd.Parameters.AddWithValue("@custoHora", custoHora);
            cmd.Parameters.AddWithValue("@horasDiarias", horasDiarias);
            cmd.Parameters.AddWithValue("@diaPagamento", diaPagamento);
            cmd.Parameters.AddWithValue("@dataAdimissao", dataAdimissao);
            cmd.Parameters.AddWithValue("@idSetor", idSetor);
           // cmd.Parameters.AddWithValue("@password", password);
            //cmd.Parameters.AddWithValue("@username", username);
            cmd.Parameters.AddWithValue("@cep", cep);
            cmd.Parameters.AddWithValue("@endereco", endereco);
            cmd.Parameters.AddWithValue("@cidade", cidade);
            cmd.Parameters.AddWithValue("@estado", estado);
            cmd.Parameters.AddWithValue("@numero", numero);
            //cmd.Parameters.AddWithValue("@tipoUsuario", tipoUsuario);
            cmd.ExecuteNonQuery();

            conn.Close();
        }

        [WebMethod]
        public void InserirSubtarefa(string titulo, string descricao, int prioridade, int tempoEstimado, int id_tarefa)
        {
            string strConn = "Data Source=localhost;"
               + "Initial Catalog=PDSD;"
               + "User ID=sa;Password=senha";
            SqlConnection conn = new SqlConnection(strConn);
            SqlCommand cmd = new SqlCommand("insert into SubTarefa values("
                + "@titulo, @descricao, @prioridade,"
		        + "@finalizado, @hora, @minuto, @porcentagem,"
		        + "@status, @tempoEstimado, @tempoTotalTrabalho,"
                + "@dataInicio, @id_tarefa)", conn);
            conn.Open();

            //cmd.Parameters.AddWithValue("@id", 82);
            cmd.Parameters.AddWithValue("@titulo", titulo);
            cmd.Parameters.AddWithValue("@descricao", descricao);
            cmd.Parameters.AddWithValue("@prioridade", prioridade);
            cmd.Parameters.AddWithValue("@finalizado", 0);
            cmd.Parameters.AddWithValue("@hora", 0);
            cmd.Parameters.AddWithValue("@minuto", 0);
            cmd.Parameters.AddWithValue("@porcentagem", 0);
            cmd.Parameters.AddWithValue("@status", "Pausado");
            cmd.Parameters.AddWithValue("@tempoEstimado", tempoEstimado);
            cmd.Parameters.AddWithValue("@tempoTotalTrabalho", 0);
            cmd.Parameters.AddWithValue("@dataInicio", DateTime.Now);
            cmd.Parameters.AddWithValue("@id_tarefa", id_tarefa);

            cmd.ExecuteNonQuery();

            conn.Close();
        
        }

        [WebMethod]
        public void AtualizarTarefa(int id, string descricao, string grauTarefa, int prioridade, int tempoEstimado, string tipoTarefa, string tituloTarefa, int projeto_id, int usuario_id)
        {
            string strConn = "Data Source=localhost;"
                + "Initial Catalog=PDSD;"
                + "User ID=sa;Password=senha";
            SqlConnection conn = new SqlConnection(strConn);
            SqlCommand cmd = new SqlCommand("update Tarefa set descricao = @descricao, grauTarefa = @grauTarefa, "
                +"prioridade = @prioridade, tempoEstimado = @tempoEstimado, tipoTarefa = @tipoTarefa, tituloTarefa = @tituloTarefa, "
                +"projeto_id = @projeto_id, usuario_id = @usuario_id where id = @id", conn);
            conn.Open();

            cmd.Parameters.AddWithValue("@id", id);
            cmd.Parameters.AddWithValue("@descricao", descricao);
            cmd.Parameters.AddWithValue("@grauTarefa", grauTarefa);
            cmd.Parameters.AddWithValue("@prioridade", prioridade);
            cmd.Parameters.AddWithValue("@tempoEstimado", tempoEstimado);
            cmd.Parameters.AddWithValue("@tipoTarefa", tipoTarefa.ToLower());
            cmd.Parameters.AddWithValue("@tituloTarefa", tituloTarefa);
            cmd.Parameters.AddWithValue("@projeto_id", projeto_id);
            cmd.Parameters.AddWithValue("@usuario_id", usuario_id);

            cmd.ExecuteNonQuery();

            conn.Close();
        }

        [WebMethod]
        public void InserirSetor(string nome)
        {
            string strConn = "Data Source=localhost;"
                + "Initial Catalog=PDSD;"
                + "User ID=sa;Password=senha";
            SqlConnection conn = new SqlConnection(strConn);
            SqlCommand cmd = new SqlCommand("insert into Setor values(@nome)", conn);
            conn.Open();
            
           // cmd.Parameters.AddWithValue("@id", id);
            cmd.Parameters.AddWithValue("@nome", nome);
            cmd.ExecuteNonQuery();
            conn.Close();
        }

        [WebMethod]
        public void InserirProjeto(string nomeProjeto, double valor, int cliente_id)
        {
            string strConn = "Data Source=localhost;"
                + "Initial Catalog=PDSD;"
                + "User ID=sa;Password=senha";
            SqlConnection conn = new SqlConnection(strConn);
            SqlCommand cmd = new SqlCommand("insert into Projeto values(@nomeProjeto, @valor, @cliente_id)", conn);
            conn.Open();

            cmd.Parameters.AddWithValue("@nomeProjeto", nomeProjeto);
            cmd.Parameters.AddWithValue("@valor", valor);
            cmd.Parameters.AddWithValue("@cliente_id", cliente_id);
            cmd.ExecuteNonQuery();
            conn.Close();
        }

        [WebMethod]
        public void AtualizarProjeto(int id, string nomeProjeto, double valor, int cliente_id)
        {
            string strConn = "Data Source=localhost;"
                + "Initial Catalog=PDSD;"
                + "User ID=sa;Password=senha";
            SqlConnection conn = new SqlConnection(strConn);
            SqlCommand cmd = new SqlCommand("update Projeto set nomeProjeto = @nomeProjeto, valor = @valor, cliente_id = @cliente_id where id = @id", conn);
            conn.Open();

            cmd.Parameters.AddWithValue("@id", id);
            cmd.Parameters.AddWithValue("@nomeProjeto", nomeProjeto);
            cmd.Parameters.AddWithValue("@valor", valor);
            cmd.Parameters.AddWithValue("@cliente_id", cliente_id);
            cmd.ExecuteNonQuery();

            conn.Close();
        }

        [WebMethod]
        public void AtualizarUsuario(int id, string password, string tipoUsuario, string username, int setor_id, string nome)
        {
            string strConn = "Data Source=localhost;"
                + "Initial Catalog=PDSD;"
                + "User ID=sa;Password=senha";
            SqlConnection conn = new SqlConnection(strConn);
            SqlCommand cmd = new SqlCommand("update Usuario set password = @password, tipoUsuario = @tipoUsuario, username = @username, setor_id = @setor_id, nome = @nome where id = @id", conn);
            conn.Open();

            cmd.Parameters.AddWithValue("@id", id);
            cmd.Parameters.AddWithValue("@password", password);
            cmd.Parameters.AddWithValue("@tipoUsuario", tipoUsuario);
            cmd.Parameters.AddWithValue("@username", username);
            cmd.Parameters.AddWithValue("@setor_id", setor_id);
            cmd.Parameters.AddWithValue("@nome", nome);
            cmd.ExecuteNonQuery();

            conn.Close();
        }

        [WebMethod]
        public void AtualizarSetor(int id, string nome)
        {
            string strConn = "Data Source=localhost;"
                + "Initial Catalog=PDSD;"
                + "User ID=sa;Password=senha";
            SqlConnection conn = new SqlConnection(strConn);
            SqlCommand cmd = new SqlCommand("update Setor set nome = @nome where id = @id", conn);
            conn.Open();

            cmd.Parameters.AddWithValue("@id", id);
            cmd.Parameters.AddWithValue("@nome",nome);
            cmd.ExecuteNonQuery();

            conn.Close();
        }

        [WebMethod]
        public void AtualizarSubtarefa(int id, string descricao, int prioridade, int tempoEstimado, string titulo, int id_tarefa)
        {
            string strConn = "Data Source=localhost;"
               + "Initial Catalog=PDSD;"
               + "User ID=sa;Password=senha";
            SqlConnection conn = new SqlConnection(strConn);
            SqlCommand cmd = new SqlCommand("update SubTarefa set "
                + "descricao = @descricao,prioridade = @prioridade,"
                + "tempoEstimado = @tempoEstimado,titulo = @titulo,"
                + "id_tarefa = @id_tarefa where id = @id", conn);
            conn.Open();

            cmd.Parameters.AddWithValue("@id", id);
            cmd.Parameters.AddWithValue("@descricao", descricao);
            cmd.Parameters.AddWithValue("@prioridade", prioridade);
           // cmd.Parameters.AddWithValue("@finalizado", 0);
           // cmd.Parameters.AddWithValue("@hora", 0);
           // cmd.Parameters.AddWithValue("@minuto", 0);
            //cmd.Parameters.AddWithValue("@porcentagem", 0);
            //cmd.Parameters.AddWithValue("@status", status);
            cmd.Parameters.AddWithValue("@tempoEstimado", tempoEstimado);
            cmd.Parameters.AddWithValue("@titulo", titulo);
            //cmd.Parameters.AddWithValue("@tempoTotalTrabalho", 0);
           // cmd.Parameters.AddWithValue("@dataInicio", DateTime.Now);
            cmd.Parameters.AddWithValue("@id_tarefa", id_tarefa);

            cmd.ExecuteNonQuery();

            conn.Close();

        }

        [WebMethod]
        public void ExcluirUsuario(int id)
        {
            string strConn = "Data Source=localhost;"
                + "Initial Catalog=PDSD;"
                + "User ID=sa;Password=senha";
            SqlConnection conn = new SqlConnection(strConn);
            SqlCommand cmd = new SqlCommand("delete from Usuario where id = @id", conn);
            conn.Open();
            cmd.Parameters.AddWithValue("@id", id);
            cmd.ExecuteNonQuery();

            conn.Close();
        }

        [WebMethod]
        public void ExcluirCliente(int id)
        {
            string strConn = "Data Source=localhost;"
                + "Initial Catalog=PDSD;"
                + "User ID=sa;Password=senha";
            SqlConnection conn = new SqlConnection(strConn);
            SqlCommand cmd = new SqlCommand("delete from Cliente where id = @id", conn);
            conn.Open();
            cmd.Parameters.AddWithValue("@id", id);            
            cmd.ExecuteNonQuery();

            conn.Close();
        }

        [WebMethod]
        public void ExcluirFuncionario(int id)
        {
            string strConn = "Data Source=localhost;"
                + "Initial Catalog=PDSD;"
                + "User ID=sa;Password=senha";
            SqlConnection conn = new SqlConnection(strConn);
            SqlCommand cmd = new SqlCommand("delete from Funcionario where id = @id", conn);
            conn.Open();
            cmd.Parameters.AddWithValue("@id", id);
            cmd.ExecuteNonQuery();

            conn.Close();
        }

        [WebMethod]
        public void ExcluirProjeto(int id)
        {
            string strConn = "Data Source=localhost;"
                + "Initial Catalog=PDSD;"
                + "User ID=sa;Password=senha";
            SqlConnection conn = new SqlConnection(strConn);
            SqlCommand cmd = new SqlCommand("delete from Projeto where id = @id", conn);
            conn.Open();
            cmd.Parameters.AddWithValue("@id", id);
            cmd.ExecuteNonQuery();

            conn.Close();
        }

        [WebMethod]
        public void ExcluirSetor(int id)
        {
            string strConn = "Data Source=localhost;"
                + "Initial Catalog=PDSD;"
                + "User ID=sa;Password=senha";
            SqlConnection conn = new SqlConnection(strConn);
            SqlCommand cmd = new SqlCommand("delete from Setor where id = @id", conn);
            conn.Open();
            cmd.Parameters.AddWithValue("@id", id);
            cmd.ExecuteNonQuery();

            conn.Close();
        }
        
        [WebMethod]
        public void ExcluirTarefa(int id)
        {
            string strConn = "Data Source=localhost;"
                + "Initial Catalog=PDSD;"
                + "User ID=sa;Password=senha";
            SqlConnection conn = new SqlConnection(strConn);
            SqlCommand cmd = new SqlCommand("delete from Tarefa where id = @id", conn);
            conn.Open();
            cmd.Parameters.AddWithValue("@id", id);
            cmd.ExecuteNonQuery();

            conn.Close();
        }
        
        [WebMethod]
        public void ExcluirSubTarefa(int id)
        {
            string strConn = "Data Source=localhost;"
                + "Initial Catalog=PDSD;"
                + "User ID=sa;Password=senha";
            SqlConnection conn = new SqlConnection(strConn);
            SqlCommand cmd = new SqlCommand("delete from SubTarefa where id = @id", conn);
            conn.Open();
            cmd.Parameters.AddWithValue("@id", id);
            cmd.ExecuteNonQuery();

            conn.Close();
        }

        [WebMethod]
        public List<Cliente> SelecionarClientes()
        {
            List<Cliente> lista = new List<Cliente>();

            string strConn = "Data Source=localhost;"
                + "Initial Catalog=PDSD;"
                + "User ID=sa;Password=senha";
            SqlConnection conn = new SqlConnection(strConn);
            SqlCommand cmd = new SqlCommand("select * from Cliente", conn);
            conn.Open();
            SqlDataReader dr = cmd.ExecuteReader();

            while (dr.Read())
            {
                Cliente c = new Cliente();
           
                c.id = Convert.ToInt32(dr["id"].ToString());
                c.nome = dr["nome"].ToString();
                c.apelido = dr["apelido"].ToString();
                c.webSite = dr["webSite"].ToString();
                c.dataNascimento = dr["dataNascimento"].ToString();
                c.sexo = dr["sexo"].ToString();
                c.cep = dr["cep"].ToString();
                c.endereco = dr["endereco"].ToString();
                c.complemento = dr["complemento"].ToString();
                c.numero = dr["numero"].ToString();
                c.bairro = dr["bairro"].ToString();
                lista.Add(c);
            }
            dr.Close();
            conn.Close();
            return lista;
        }

        [WebMethod]
        public List<Funcionario> SelecionarFuncionario()
        {
            List<Funcionario> lista = new List<Funcionario>();

            string strConn = "Data Source=localhost;"
                + "Initial Catalog=PDSD;"
                + "User ID=sa;Password=senha";
            SqlConnection conn = new SqlConnection(strConn);
            SqlCommand cmd = new SqlCommand("select * from Funcionario", conn);
            conn.Open();
            SqlDataReader dr = cmd.ExecuteReader();

            while (dr.Read())
            {
                Funcionario f = new Funcionario();

                f.id = Convert.ToInt32(dr["id"].ToString());
                f.nome = dr["nome"].ToString();
                f.salario = Convert.ToInt32(dr["salario"].ToString());
                f.cargo = dr["cargo"].ToString();
                f.custoHora = Convert.ToInt32(dr["custoHora"].ToString());
                f.horasDiarias = Convert.ToInt32(dr["horasDiarias"].ToString());
                f.diaPagamento = Convert.ToInt32(dr["diaPagamento"].ToString());
                f.dataAdmissao = dr["dataAdimissao"].ToString();
                f.setor_id = Convert.ToInt32(dr["idSetor"].ToString());
                //f.password = dr["password"].ToString();
                //f.username = dr["username"].ToString();
                f.cep = dr["cep"].ToString();
                f.endereco = dr["endereco"].ToString();
                f.cidade = dr["cidade"].ToString();
                f.estado = dr["estado"].ToString();
                //f.numero = int.Parse(dr["numero"].ToString());
               // f.tipoUsuario = dr["tipoUsuario"].ToString();
                lista.Add(f);
            }
            dr.Close();
            conn.Close();
            return lista;
        }

        [WebMethod]
        public List<Tarefa> SelecionarTarefa()
        {
            List<Tarefa> lista = new List<Tarefa>();

            string strConn = "Data Source=localhost;"
                + "Initial Catalog=PDSD;"
                + "User ID=sa;Password=senha";
            SqlConnection conn = new SqlConnection(strConn);
            SqlCommand cmd = new SqlCommand("select * from Tarefa", conn);
            conn.Open();
            SqlDataReader dr = cmd.ExecuteReader();

            while (dr.Read())
            {
                Tarefa t = new Tarefa();

                t.id = Convert.ToInt32(dr["id"].ToString());
                t.dataCadastro = dr["datacadastro"].ToString();
                t.dataInicio = dr["dataInicio"].ToString();
                t.descricao = dr["descricao"].ToString();
                t.tituloTarefa = dr["tituloTarefa"].ToString();
                t.tempoEstimado =  Convert.ToInt32(dr["tempoEstimado"].ToString());
                t.tempoTotalTrabalho = Convert.ToDouble(dr["tempoTotalTrabalho"].ToString());
                t.hora = Convert.ToInt32(dr["hora"].ToString());
                t.minuto = Convert.ToInt32(dr["minuto"].ToString());
                t.status = dr["status"].ToString();
                t.tipoTarefa = dr["tipotarefa"].ToString();
                t.projeto_id = Convert.ToInt32(dr["projeto_id"].ToString());
                t.usuario_id = Convert.ToInt32(dr["usuario_id"].ToString());
                //t.finalizado = Convert.ToBoolean(dr["finalizado"].ToString());
                t.porcentagem = Convert.ToInt32(dr["porcentagem"].ToString());
                t.grauTarefa = dr["grauTarefa"].ToString();
                t.prioridade = Convert.ToInt32(dr["prioridade"].ToString());

                lista.Add(t);
            }
            dr.Close();
            conn.Close();
            return lista;
        }

        [WebMethod]
        public List<Setor> SelecionarSetor()
        {
            List<Setor> lista = new List<Setor>();

            string strConn = "Data Source=localhost;"
                + "Initial Catalog=PDSD;"
                + "User ID=sa;Password=senha";
            SqlConnection conn = new SqlConnection(strConn);
            SqlCommand cmd = new SqlCommand("select * from Setor", conn);
            conn.Open();
            SqlDataReader dr = cmd.ExecuteReader();

            while (dr.Read())
            {
                Setor s = new Setor();

                s.id = Convert.ToInt32(dr["id"].ToString());
                s.nome = dr["nome"].ToString();

                lista.Add(s);
            }
            dr.Close();
            conn.Close();
            return lista;
        }

        [WebMethod]
        public List<Usuario> SelecionarUsuario()
        {
            List<Usuario> lista = new List<Usuario>();

            string strConn = "Data Source=localhost;"
                + "Initial Catalog=PDSD;"
                + "User ID=sa;Password=senha";
            SqlConnection conn = new SqlConnection(strConn);
            SqlCommand cmd = new SqlCommand("select * from Usuario", conn);
            conn.Open();
            SqlDataReader dr = cmd.ExecuteReader();

            while (dr.Read())
            {
                Usuario u = new Usuario();

                u.id = Convert.ToInt32(dr["id"].ToString());
                u.password = dr["password"].ToString();
                u.tipoUsuario = dr["tipoUsuario"].ToString();
                u.username = dr["username"].ToString();
                u.setor_id = Convert.ToInt32(dr["setor_id"].ToString());
                u.nome = dr["nome"].ToString();

                lista.Add(u);

            }
            dr.Close();
            conn.Close();
            return lista;
        }

        [WebMethod]
        public List<SubTarefa> SelecionarSubTarefa()
        {
            List<SubTarefa> lista = new List<SubTarefa>();

            string strConn = "Data Source=localhost;"
                + "Initial Catalog=PDSD;"
                + "User ID=sa;Password=senha";
            SqlConnection conn = new SqlConnection(strConn);
            SqlCommand cmd = new SqlCommand("select * from SubTarefa", conn);
            conn.Open();
            SqlDataReader dr = cmd.ExecuteReader();

            while (dr.Read())
            {
                SubTarefa s = new SubTarefa();

                s.id = Convert.ToInt32(dr["id"].ToString());
                s.titulo = dr["titulo"].ToString();
                s.descricao = dr["descricao"].ToString();
                s.prioridade = Convert.ToInt32(dr["prioridade"].ToString());
                //s.finalizado = Convert.ToBoolean(dr["finalizado"].ToString());
                s.hora = Convert.ToInt32(dr["hora"].ToString());
                s.minuto = Convert.ToInt32(dr["minuto"].ToString());
                s.porcentagem = Convert.ToInt32(dr["porcentagem"].ToString());
                s.status = dr["status"].ToString();
                s.tempoEstimado = Convert.ToInt32(dr["tempoEstimado"].ToString());
                s.tempoTotalTrabalho = Convert.ToInt32(dr["tempoTotalTrabalho"].ToString());
                s.dataInicio = dr["dataInicio"].ToString();
                s.id_tarefa = Convert.ToInt32(dr["id_tarefa"].ToString());
              
                lista.Add(s);
            }
            dr.Close();
            conn.Close();
            
            return lista;
        }

        [WebMethod]
        public List<Usuario> ListarUsuarios()
        {
            List<Usuario> lista = new List<Usuario>();
            string strConn = "Data Source=localhost;"
                + "Initial Catalog=PDSD;"
                + "User ID=sa;Password=senha";
            SqlConnection conn = new SqlConnection(strConn);
            SqlCommand cmd = new SqlCommand("select * from Usuario", conn);
            conn.Open();
            SqlDataReader dr = cmd.ExecuteReader();

            while (dr.Read())
            {
                Usuario c = new Usuario();
                c.id = Convert.ToInt32(dr["id"].ToString());
                c.nome = dr["nome"].ToString();
                c.username = dr["username"].ToString();
                c.password = dr["password"].ToString();

                lista.Add(c);
            }
            dr.Close();
            conn.Close();
            return lista;
        }

        [WebMethod]
        public List<Tarefa> BuscarTarefa(int id)
        {
            Tarefa c = new Tarefa();
            List<Tarefa> lista = new List<Tarefa>();
            
            string strConn = "Data Source=localhost;"
                + "Initial Catalog=PDSD;"
                + "User ID=sa;Password=senha";
            SqlConnection conn = new SqlConnection(strConn);
            SqlCommand cmd = new SqlCommand("select id, tituloTarefa, descricao, tipoTarefa, prioridade, grauTarefa, tempoEstimado, projeto_id, usuario_id from Tarefa where id = @id", conn);
            conn.Open();
            cmd.Parameters.AddWithValue("@id", id);
            SqlDataReader dr = cmd.ExecuteReader();

            while (dr.Read())
            {
                c.id = Convert.ToInt32(dr["id"].ToString());
                c.tituloTarefa = dr["tituloTarefa"].ToString();
                c.descricao = dr["descricao"].ToString();
                c.tipoTarefa = dr["tipoTarefa"].ToString();
                c.prioridade = Convert.ToInt32(dr["prioridade"].ToString());
                c.grauTarefa = dr["grauTarefa"].ToString();
                c.tempoEstimado = Convert.ToInt32(dr["tempoEstimado"].ToString());
                c.projeto_id = Convert.ToInt32(dr["projeto_id"].ToString());
                c.usuario_id = Convert.ToInt32(dr["usuario_id"].ToString());
                lista.Add(c);
            }
            dr.Close();
            conn.Close();
            return lista;
        }

        [WebMethod]
        public List<Projeto> ListarProjetos()
        {
            List<Projeto> lista = new List<Projeto>();
            string strConn = "Data Source=localhost;"
                + "Initial Catalog=PDSD;"
                + "User ID=sa;Password=senha";
            SqlConnection conn = new SqlConnection(strConn);
            SqlCommand cmd = new SqlCommand("select * from Projeto", conn);
            conn.Open();
            SqlDataReader dr = cmd.ExecuteReader();

            while (dr.Read())
            {
                Projeto c = new Projeto();
                c.id = Convert.ToInt32(dr["id"].ToString());
                c.nomeProjeto = dr["nomeProjeto"].ToString();
                c.valor = Convert.ToInt32(dr["valor"].ToString());
                c.cliente_id = Convert.ToInt32(dr["cliente_id"].ToString());

                lista.Add(c);
            }
            dr.Close();
            conn.Close();
            return lista;
        }

        [WebMethod]
        public List<Tarefa> ListarTarefasByUsuario(int id)
        {
            List<Tarefa> lista = new List<Tarefa>();
            string strConn = "Data Source=localhost;"
                + "Initial Catalog=PDSD;"
                + "User ID=sa;Password=senha";
            SqlConnection conn = new SqlConnection(strConn);
            SqlCommand cmd = new SqlCommand("select * from Tarefa where Tarefa.usuario_id = @id", conn);
            conn.Open();
            cmd.Parameters.AddWithValue("@id", id);
            SqlDataReader dr = cmd.ExecuteReader();

            while (dr.Read())
            {
                Tarefa c = new Tarefa();
                c.id = Convert.ToInt32(dr["id"].ToString());
                c.tituloTarefa = dr["tituloTarefa"].ToString();
                c.porcentagem = Double.Parse(dr["porcentagem"].ToString());
                c.tempoEstimado = Convert.ToInt32(dr["tempoEstimado"].ToString());
                c.tempoTotalTrabalho = Double.Parse(dr["tempoTotalTrabalho"].ToString());
                c.descricao = dr["descricao"].ToString();

                lista.Add(c);
            }
            dr.Close();
            conn.Close();
            return lista;
        }

        [WebMethod]
        public List<Tarefa> ListarTarefasByProjeto(int id)
        {
            List<Tarefa> lista = new List<Tarefa>();
            string strConn = "Data Source=localhost;"
                + "Initial Catalog=PDSD;"
                + "User ID=sa;Password=senha";
            SqlConnection conn = new SqlConnection(strConn);
            SqlCommand cmd = new SqlCommand("select id, tituloTarefa, porcentagem, tempoEstimado, tempoTotalTrabalho from Tarefa where Tarefa.projeto_id = @id", conn);
            conn.Open();
            cmd.Parameters.AddWithValue("@id", id);
            SqlDataReader dr = cmd.ExecuteReader();

            while (dr.Read())
            {
                Tarefa c = new Tarefa();
                c.id = Convert.ToInt32(dr["id"].ToString());
                c.tituloTarefa = dr["tituloTarefa"].ToString();
                c.porcentagem = Double.Parse(dr["porcentagem"].ToString());
                c.tempoEstimado = Convert.ToInt32(dr["tempoEstimado"].ToString());
                c.tempoTotalTrabalho = Double.Parse(dr["tempoTotalTrabalho"].ToString());

                lista.Add(c);
            }
            dr.Close();
            conn.Close();
            return lista;
        }

        [WebMethod]
        public Usuario ValidarLoginAdmin(String username, String senha)
        {
            Usuario c = new Usuario();

            string strConn = "Data Source=localhost;"
                + "Initial Catalog=PDSD;"
                + "User ID=sa;Password=senha";
            SqlConnection conn = new SqlConnection(strConn);
            SqlCommand cmd = new SqlCommand("select * from Usuario where username = @username and password = @password and tipoUsuario = 'Administrador'", conn);
            conn.Open();
            cmd.Parameters.AddWithValue("@username", username);
            cmd.Parameters.AddWithValue("@password", senha);
            SqlDataReader dr = cmd.ExecuteReader();

            while (dr.Read())
            {
                c.id = Convert.ToInt32(dr["id"].ToString());
                c.nome = dr["nome"].ToString();
                c.username = dr["username"].ToString();
                c.password = dr["password"].ToString();
            }
            conn.Close();
            

            return c;

        }

        [WebMethod]
        public DataTable BuscaCliente(String filtro) {

            System.Data.SqlClient.SqlConnection conn = new System.Data.SqlClient.SqlConnection("Data Source=localhost;Initial Catalog=PDSD;Persist Security Info=True;User ID=sa;Password=senha");
            System.Data.DataSet ds = new System.Data.DataSet();
            System.Data.SqlClient.SqlDataAdapter adapter = new System.Data.SqlClient.SqlDataAdapter();
            adapter.SelectCommand = new System.Data.SqlClient.SqlCommand("select * from Cliente where nome like '" + filtro + "%'", conn);
            adapter.Fill(ds, "Cliente");
            return ds.Tables["Cliente"];
        }

        [WebMethod]
        public DataTable BuscaFuncionario(String filtro)
        {

            System.Data.SqlClient.SqlConnection conn = new System.Data.SqlClient.SqlConnection("Data Source=localhost;Initial Catalog=PDSD;Persist Security Info=True;User ID=sa;Password=senha");
            System.Data.DataSet ds = new System.Data.DataSet();
            System.Data.SqlClient.SqlDataAdapter adapter = new System.Data.SqlClient.SqlDataAdapter();
            adapter.SelectCommand = new System.Data.SqlClient.SqlCommand("select * from Funcionario where nome like '" + filtro + "%'", conn);
            adapter.Fill(ds, "Funcionario");
            return ds.Tables["Funcionario"];
        }

        [WebMethod]
        public DataTable BuscaTarefa(String filtro)
        {

            System.Data.SqlClient.SqlConnection conn = new System.Data.SqlClient.SqlConnection("Data Source=localhost;Initial Catalog=PDSD;Persist Security Info=True;User ID=sa;Password=senha");
            System.Data.DataSet ds = new System.Data.DataSet();
            System.Data.SqlClient.SqlDataAdapter adapter = new System.Data.SqlClient.SqlDataAdapter();
            adapter.SelectCommand = new System.Data.SqlClient.SqlCommand("select * from Tarefa where tituloTarefa like '" + filtro + "%'", conn);
            adapter.Fill(ds, "Tarefa");
            return ds.Tables["Tarefa"];
        }

        [WebMethod]
        public DataTable BuscaProjeto(String filtro)
        {

            System.Data.SqlClient.SqlConnection conn = new System.Data.SqlClient.SqlConnection("Data Source=localhost;Initial Catalog=PDSD;Persist Security Info=True;User ID=sa;Password=senha");
            System.Data.DataSet ds = new System.Data.DataSet();
            System.Data.SqlClient.SqlDataAdapter adapter = new System.Data.SqlClient.SqlDataAdapter();
            adapter.SelectCommand = new System.Data.SqlClient.SqlCommand("select * from Projeto where nomeProjeto like '" + filtro + "%'", conn);
            adapter.Fill(ds, "Projeto");
            return ds.Tables["Projeto"];
        }

        [WebMethod]
        public DataTable ComboProjeto()
        {

            System.Data.SqlClient.SqlConnection conn = new System.Data.SqlClient.SqlConnection("Data Source=localhost;Initial Catalog=PDSD;Persist Security Info=True;User ID=sa;Password=senha");
            System.Data.DataSet ds = new System.Data.DataSet();
            System.Data.SqlClient.SqlDataAdapter adapter = new System.Data.SqlClient.SqlDataAdapter();
            adapter.SelectCommand = new System.Data.SqlClient.SqlCommand("Select id, nome from Cliente", conn);
            adapter.Fill(ds, "Cliente");
            return ds.Tables["Cliente"];
        }

        [WebMethod]
        public DataTable ComboTarefaProjeto()
        {

            System.Data.SqlClient.SqlConnection conn = new System.Data.SqlClient.SqlConnection("Data Source=localhost;Initial Catalog=PDSD;Persist Security Info=True;User ID=sa;Password=senha");
            System.Data.DataSet ds = new System.Data.DataSet();
            System.Data.SqlClient.SqlDataAdapter adapter = new System.Data.SqlClient.SqlDataAdapter();
            adapter.SelectCommand = new System.Data.SqlClient.SqlCommand("Select id, nomeProjeto from Projeto", conn);
            adapter.Fill(ds, "Projeto");
            return ds.Tables["Projeto"];
        }

        [WebMethod]
        public DataTable ComboTarefaUsuario()
        {

            System.Data.SqlClient.SqlConnection conn = new System.Data.SqlClient.SqlConnection("Data Source=localhost;Initial Catalog=PDSD;Persist Security Info=True;User ID=sa;Password=senha");
            System.Data.DataSet ds = new System.Data.DataSet();
            System.Data.SqlClient.SqlDataAdapter adapter = new System.Data.SqlClient.SqlDataAdapter();
            adapter.SelectCommand = new System.Data.SqlClient.SqlCommand("Select id, nome from Usuario", conn);
            adapter.Fill(ds, "Usuario");
            return ds.Tables["Usuario"];
        }

        [WebMethod]
        public DataTable ComboSetor()
        {

            System.Data.SqlClient.SqlConnection conn = new System.Data.SqlClient.SqlConnection("Data Source=localhost;Initial Catalog=PDSD;Persist Security Info=True;User ID=sa;Password=senha");
            System.Data.DataSet ds = new System.Data.DataSet();
            System.Data.SqlClient.SqlDataAdapter adapter = new System.Data.SqlClient.SqlDataAdapter();
            adapter.SelectCommand = new System.Data.SqlClient.SqlCommand("Select id, Nome from Setor", conn);
            adapter.Fill(ds, "Setor");
            return ds.Tables["Setor"];
        }
    }
}